﻿using System;
using System.Collections.Generic;
using System.Text;
using Framework;

namespace BLL.Common
{
    /// <summary>EasyUI
    /// Add By Rivorse 20161110
    /// </summary>
    public class EasyUIHelper : BusinessBase
    {
        /// <summary>取得分页的语句
        /// Add By Rivorse 20161110
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="OrderByField"></param>
        /// <param name="dicLike"></param>
        /// <param name="dicEqual"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNum"></param>
        /// <returns></returns>
        public static string GetSqlOfPageData(string TableName, string OrderByField,
            Dictionary<string, string> dicLike, Dictionary<string, string> dicEqual,
            int? pageSize, int? pageNum)
        {
            string strSql = "";
            string condition = getCondition(dicLike, dicEqual);
            string conditionWithWhere = "";
            if (!string.IsNullOrEmpty(condition))
            {
                conditionWithWhere = " where 1 = 1 " + condition;
            }

            if (pageSize.HasValue && pageNum.HasValue)
            {
                int from = pageSize.Value * (pageNum.Value - 1);
                int to = pageSize.Value * pageNum.Value;
                strSql = @"
select top {2} * from {0} where {5} > (
	select ISNULL(MAX({5}), 0) from (
		select top {1} * from {0} {3} order by {5}
	) a
) b {4} order by {5} ";
                strSql = string.Format(strSql, TableName, from, pageSize, conditionWithWhere, condition, OrderByField);
            }
            else
            {
                strSql = string.Format(@"SELECT * FROM {0} {1} ORDER BY {2}", TableName, conditionWithWhere, OrderByField);
            }

            return strSql;
        }

        /// <summary>取得easyUI dataGrid取数据用的SQL语句
        /// 
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="RowsCountOnePage"></param>
        /// <param name="TableName"></param>
        /// <param name="OrderFieldName"></param>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public static string GetSqlOfPageData(int? pageNum, int? pageSize,
            string TableName, string KPFieldName, string OrderFieldName,
            string TableFilter)
        {
            if (string.IsNullOrEmpty(TableName) || string.IsNullOrEmpty(KPFieldName))
            {
                throw new Exception("取数据时表名或者主键字段不能为空！");
            }

            string strSql = @"
select top {0} * from {2} where {3} > (
    select ISNULL(MAX({3}), 0) from (select top {1} {3} from {2} {4} order by {3}) A
) {5} order by {6} "; //查询类别信息表
            strSql = string.Format(strSql, pageSize, (pageNum - 1) * pageSize,
                TableName, KPFieldName,
                string.IsNullOrEmpty(TableFilter) ? "" : " where " + TableFilter,
                string.IsNullOrEmpty(TableFilter) ? "" : " and " + TableFilter,
                string.IsNullOrEmpty(OrderFieldName) ? KPFieldName : OrderFieldName);

            return strSql;
        }

        /// <summary>取得easyUI dataGrid取数据用的SQL语句
        /// 主键为字符串时
        /// </summary>
        /// <param name="pageNum"></param>
        /// <param name="pageSize"></param>
        /// <param name="TableName"></param>
        /// <param name="KPFieldName"></param>
        /// <param name="OrderFieldName"></param>
        /// <param name="TableFilter"></param>
        /// <returns></returns>
        public static string GetSqlOfPageData2(int? pageNum, int? pageSize,
            string TableName, string KPFieldName, string OrderFieldName,
            string TableFilter)
        {
            if (string.IsNullOrEmpty(TableName) || string.IsNullOrEmpty(KPFieldName))
            {
                throw new Exception("取数据时表名或者主键字段不能为空！");
            }

            string strSql = @"
select top {0} * from {2} where {3} > (
    select ISNULL(MAX({3}), ' ') from (select top {1} {3} from {2} {4} order by {3}) A
) {5} order by {6} "; //查询类别信息表
            strSql = string.Format(strSql, pageSize, (pageNum - 1) * pageSize,
                TableName, KPFieldName,
                string.IsNullOrEmpty(TableFilter) ? "" : " where " + TableFilter,
                string.IsNullOrEmpty(TableFilter) ? "" : " and " + TableFilter,
                string.IsNullOrEmpty(OrderFieldName) ? KPFieldName : OrderFieldName);

            return strSql;
        }
    }
}
